--EXEC sp_get_DocInformacion '0000000000','0000000000','000000','5','6318,6319, 6320, 6321, 6322, 6323, 6324, 6325,6326, 6328','1,2'

ALTER PROCEDURE sp_get_DocInformacion
    @cPerCodigo VARCHAR(10) ,
    @cPerDestCodigo VARCHAR(10) = 'ALL' ,
    @cDocCodigo VARCHAR(20) = 'ALL' ,
    @cDocPerTipo VARCHAR(20) ,
    @cDocEstado VARCHAR(20) ,
    @cDocConTipo VARCHAR(20)
AS 
    BEGIN

        DECLARE @sql VARCHAR(MAX)

        SET @sql = '
        SELECT DISTINCT
                p.cPerApellido + SPACE(2) + p.cPerNombre AS PerDestino ,
                p.cPerCodigo AS CodPerDestino ,
                p2.cPerApellido + SPACE(2) + p2.cPerNombre AS PerRemite ,
                p2.cPerCodigo AS CodPerRemite ,
                d.nDocTipo ,
                d.nEstado ,
                CONVERT(VARCHAR(5000), dc.tDocConContenido) ,
                d.cDocObserv ,
                dc.nDocConTipo ,
                dv.dFechaIni ,
                di.cDocNDoc ,
                duo.nUniOrgCodigo ,
                d.cDocCodigo ,
                dpe.nUniOrgCodigo AS UODestino
        FROM    dbo.DocUniOrg duo
                INNER JOIN dbo.Documentos d ON duo.cDocCodigo = d.cDocCodigo
                INNER JOIN dbo.DocIdentifica di ON d.cDocCodigo = di.cDocCodigo
                INNER JOIN dbo.DocPersona dp ON d.cDocCodigo = dp.cDocCodigo
                INNER JOIN dbo.DocContenido dc ON d.cDocCodigo = dc.cDocCodigo
                INNER JOIN dbo.Persona p ON dp.cPerCodigo = p.cPerCodigo
                INNER JOIN dbo.DocPersona dp2 ON d.cDocCodigo = dp2.cDocCodigo
                INNER JOIN dbo.Persona p2 ON dp2.cPerCodigo = p2.cPerCodigo
                INNER JOIN dbo.DocVigencia dv ON d.cDocCodigo = dv.cDocCodigo
                INNER JOIN dbo.DocPersona dp3 ON d.cDocCodigo = dp3.cDocCodigo
                INNER JOIN dbo.DocPerEdit dpe ON dp.cDocCodigo = dpe.cDocCodigo
                                                 AND dp.cPerCodigo = dpe.cPerCodigo
        WHERE   ( dp.nDocPerTipo = 2 )
                AND ( d.nEstado IN ( ' + @cDocEstado + ' ) )
                AND ( dp2.nDocPerTipo = 1 )
                AND ( dc.nDocConTipo IN ( ' + @cDocConTipo + ' ) )
                AND ( dp3.nDocPerTipo IN ( ' + @cDocPerTipo + ') )
                AND ( dp3.cPerCodigo IN (
                      SELECT    *
                      FROM      GetListTablaAll(''' + @cPerCodigo
            + ''', '','') ) )
                AND ( DPE.nDocPerEdiTipo IN ( 1006, 1007 ) )
                AND ( duo.nDocUniOrgTipo = 1 )
                AND DI.nDocTipoNum = 1 '
		
        IF @cDocCodigo <> 'ALL' 
            BEGIN
                SET @sql = @sql + ' AND ( dp3.cDocCodigo = ''' + @cDocCodigo + ''')'
            END  

        IF @cPerDestCodigo <> 'ALL' 
            BEGIN
                SET @sql = @sql + ' AND ( p.cPerCodigo IN (
                      SELECT    *
                      FROM      GetListTablaAll( ''' + @cPerDestCodigo
                    + ''', '','') ))'
            END


        SET @sql = @sql + ' ORDER BY dc.nDocConTipo '

        EXEC ( @sql )

    END